﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;
using System.Threading;

namespace DataAccessLayer
{
    /// <summary>    
    /// 数据访问基础类(ACCESS)    
    /// </summary>    
    public class AccessHelper : IHandleDB
    {
        //数据库连接字符串(web.config来配置)    
        //public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];    
        //public static string connectionString = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]);    
        //public static string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];
        public log4net.ILog log = log4net.LogManager.GetLogger("SqlException");
        public string connectionString = "";
        public AccessHelper(string conString)
        {
            connectionString = conString;
        }


        #region IHandleDB Members

        public int GetMaxId(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = ExecuteScalar(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public bool Exists(string strSql)
        {
            object obj = ExecuteScalar(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool Exists(string strSql, params OleDbParameter[] cmdParms)
        {
            object obj = ExecuteScalar(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public object ExecuteScalar(string SQLString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        connection.Close();
                        log.Error(string.Format("异常：{0}", e.Message));
                        return null;
                    }
                }
            }
        }

        public object ExecuteScalar(string SQLString, params OleDbParameter[] cmdParms)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        log.Error(string.Format("异常：{0}", e.Message));
                        return null;
                    }
                }
            }
        }

        public int ExecuteNonQuery(string SQLString)
        {
            object obj = new object();
            lock (obj)
            {
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (System.Data.OleDb.OleDbException e)
                        {
                            connection.Close();
                            log.Error(string.Format("异常：{0}", e.Message));
                            return 0;
                        }
                    }
                }
                Monitor.Exit(obj);
            }
        }

        public int ExecuteNonQuery(string SQLString, params OleDbParameter[] cmdParms)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        log.Error(string.Format("异常：{0}", e.Message));
                        return 0;
                    }
                }
            }
        }

        public int ExecuteSqlByTime(string SQLString, int Times)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        connection.Close();
                        log.Error(string.Format("异常：{0}", e.Message));
                        return 0;
                    }
                }
            }
        }

        public void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = connection;
                OleDbTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OleDb.OleDbException e)
                {
                    tx.Rollback();
                    connection.Close();
                    log.Error(string.Format("异常：{0}", e.Message));
                }
            }
        }

        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                using (OleDbTransaction trans = connection.BeginTransaction())
                {
                    OleDbCommand cmd = new OleDbCommand();
                    try
                    {
                        //循环    
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
                            PrepareCommand(cmd, connection, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        trans.Rollback(); 
                        connection.Close();
                        log.Error(string.Format("异常：{0}", e.Message));
                    }
                }
            }
        }

        public OleDbDataReader ExecuteReader(string strSQL)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(strSQL, connection);
            try
            {
                connection.Open();
                OleDbDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                log.Error(string.Format("异常：{0}", e.Message));
                return null;
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用    
            //{    
            //  cmd.Dispose();    
            //  connection.Close();    
            //}    
        }

        public OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                OleDbDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                log.Error(string.Format("异常：{0}", e.Message));
                return null;
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用    
            //{    
            //  cmd.Dispose();    
            //  connection.Close();    
            //}    
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet QueryPaging(string SQLString, string tbName, ref int count)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                    return ds;
                }
                catch (System.Data.OleDb.OleDbException ex)
                {
                    connection.Close();
                    log.Error(string.Format("异常：{0}", ex.Message));
                    return null;
                }
            }
        }

        public DataSet Query(string SQLString, int Times)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                    return ds;
                }
                catch (System.Data.OleDb.OleDbException ex)
                {
                    connection.Close();
                    log.Error(string.Format("异常：{0}", ex.Message));
                    return null;
                }
               
            }
        }

        public DataTable Query(string SQLString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                DataTable ds = new DataTable();
                try
                {
                    connection.Open();
                    OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
                    command.Fill(ds);
                    return ds;
                }
                catch (System.Data.OleDb.OleDbException ex)
                {
                    connection.Close();
                    log.Error(string.Format("异常：{0}", ex.Message));
                    return null;
                }
       
            }
        }

        public DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        return ds;
                    }
                    catch (System.Data.OleDb.OleDbException ex)
                    {
                        connection.Close();
                        log.Error(string.Format("异常：{0}", ex.Message));
                        return null;
                    }
                  
                }
            }
        }

        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;    
            if (cmdParms != null)
            {
                foreach (OleDbParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion

        #region 获取根据指定字段排序并分页查询。

        ///// <summary>    
        ///// 分页查询数据记录总数获取    
        ///// </summary>    
        ///// <param name="_tbName">----要显示的表或多个表的连接</param>    
        ///// <param name="_ID">----主表的主键</param>    
        ///// <param name="_strCondition">----查询条件,不需where</param>            
        ///// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>    
        ///// <returns></returns>    
        //public string getPageListCounts(string _ID, string _tbName, string _strCondition, int _Dist)
        //{
        //    //---存放取得查询结果总数的查询语句                        
        //    //---对含有DISTINCT的查询进行SQL构造    
        //    //---对含有DISTINCT的总数查询进行SQL构造    
        //    string strTmp = "", SqlSelect = "", SqlCounts = "";

        //    if (_Dist == 0)
        //    {
        //        SqlSelect = "Select ";
        //        SqlCounts = "COUNT(*)";
        //    }
        //    else
        //    {
        //        SqlSelect = "Select DISTINCT ";
        //        SqlCounts = "COUNT(DISTINCT " + _ID + ")";
        //    }
        //    if (_strCondition == string.Empty)
        //    {
        //        strTmp = SqlSelect + " " + SqlCounts + " FROM " + _tbName;
        //    }
        //    else
        //    {
        //        strTmp = SqlSelect + " " + SqlCounts + " FROM " + " Where (1=1) " + _strCondition;
        //    }
        //    return strTmp;
        //}


        ///// <summary>    
        ///// 智能返回SQL语句    
        ///// </summary>    
        ///// <param name="primaryKey">主键（不能为空）</param>    
        ///// <param name="queryFields">提取字段（不能为空）</param>    
        ///// <param name="tableName">表（理论上允许多表）</param>    
        ///// <param name="condition">条件（可以空）</param>    
        ///// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>    
        ///// <param name="pageSize">分页数（不能为空）</param>    
        ///// <param name="pageIndex">当前页，起始为：1（不能为空）</param>    
        ///// <returns></returns>    

        //public string getPageListSql(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
        //{
        //    string strTmp = ""; //---strTmp用于返回的SQL语句    
        //    string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = "";
        //    //0：分页数量    
        //    //1:提取字段    
        //    //2:表    
        //    //3:条件    
        //    //4:主键不存在的记录    
        //    //5:排序    
        //    SqlSelect = " select top {0} {1} from {2} {3} {4} {5}";
        //    //0:主键    
        //    //1:TOP数量,为分页数*(排序号-1)    
        //    //2:表    
        //    //3:条件    
        //    //4:排序    
        //    SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";

        //    if (orderBy != "")
        //        strOrderBy = " order by " + orderBy;
        //    if (condition != "")
        //        strWhere += " and " + condition;
        //    int pageindexsize = (pageIndex - 1) * pageSize;
        //    if (pageindexsize > 0)
        //    {
        //        strTop = " top " + pageindexsize.ToString();

        //        SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy);

        //        strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy);

        //    }
        //    else
        //    {
        //        strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy);

        //    }
        //    return strTmp;
        //}


        ///// <summary>    
        ///// 获取根据指定字段排序并分页查询。DataSet    
        ///// </summary>    
        ///// <param name="pageSize">每页要显示的记录的数目</param>    
        ///// <param name="pageIndex">要显示的页的索引</param>    
        ///// <param name="tableName">要查询的数据表</param>    
        ///// <param name="queryFields">要查询的字段,如果是全部字段请填写：*</param>    
        ///// <param name="primaryKey">主键字段，类似排序用到</param>    
        ///// <param name="orderBy">是否为升序排列：0为升序，1为降序</param>    
        ///// <param name="condition">查询的筛选条件</param>    
        ///// <returns>返回排序并分页查询的DataSet</returns>    
        //public  DataSet GetPagingList(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
        //{
        //    string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);

        //    return Query(sql);
        //}
        //public  string GetPagingListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
        //{
        //    string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);

        //    return sql;
        //}
        //public  int GetRecordCount(string _ID, string _tbName, string _strCondition, int _Dist)
        //{
        //    string sql = getPageListCounts(_ID, _tbName, _strCondition, _Dist);

        //    object obj = 
        //        (sql);
        //    if (obj == null)
        //    {
        //        return 1;
        //    }
        //    else
        //    {
        //        return int.Parse(obj.ToString());
        //    }
        //}

        #endregion

    }
}

//标准安全策略 

//Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\mydatabase.mdb;Uid=Admin;Pwd=; 

//工作组 

//Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\mydatabase.mdb;SystemDB=C:\\mydatabase.mdw; 

//独占模式 

//Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=; 

//管理员模式 
//如果您需要在程序中使用 CREATE USER, CREATE GROUP, ADD USER, GRANT, REVOKE 和DEFAULTS等命令，您就需要使用此连接字符串。   
//Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\mydatabase.mdb;Uid=Admin;Pwd=;ExtendedAnsiSQL=1; 

//OLE DB, OleDbConnection (.NET) 

//标准安全策略 

//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb;User Id=admin;Password=; 

//使用数据库密码 
//如果您的Access数据库设置了密码，您就需要参照如下连接字符串将密码写入后才能够正常使用。   
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword; 

//工作组(系统数据库) 

//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb;Jet OLEDB:System Database=system.mdw; 

//工作组(系统数据库)并且制定用户名和密码 

//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;   
